package com.ssyt.hiring.company.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.ssyt.hiring.common.PageModel;
import com.ssyt.hiring.common.util.DBUtil;
import com.ssyt.hiring.company.entity.ApplicationEntity;
public class ApplicationService {

	/**
	 * 判断某个求职者是否投递过某个职位
	 * 
	 * @param talentId
	 * @param id
	 * @return
	 */
	public ApplicationEntity isApplied(int talentId, int id) {
		String sql="SELECT * FROM application where talent_id=? AND position_id=?";
		return DBUtil.getUniqueResult(ApplicationEntity.class, sql, talentId,
				id);
	}
	/**
	 * 投递简历
	 * @param resumeId
	 * @param positionId
	 * @param talentId
	 * @return
	 */
	public int add(int resumeId, int positionId, int talentId) {
		String sql = "INSERT INTO application VALUES(NULL, ?, ?, ?, 1, NOW(), NULL);";
		return DBUtil.executeUpdate(sql, positionId, talentId, resumeId);
	}
	/**
	 * 分页查询某个公司所有的职位申请信息
	 * 
	 * @param application
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageModel<ApplicationEntity> listCompanyVerifyByPage(
			ApplicationEntity applicationEntity, int pageNo, int pageSize) {
		List<ApplicationEntity> applicationEntities=new ArrayList<ApplicationEntity>();
		List<Object> paramList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("p.name as 'position$name', ");
		sb.append("t.name as 'talent$name', ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.status, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, talent t,company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		sb.append("AND p.company_id = c.id ");
		sb.append("AND c.id = ? ");
		int companyId = applicationEntity.getCompanyId();
		paramList.add(companyId);
		
		String positionName = applicationEntity.getPosition$name();
		if (StringUtils.isNoneBlank(positionName)) {
			sb.append("AND p.name LIKE ? ");
			paramList.add("%" + positionName + "%");
		}
		
		int status = applicationEntity.getStatus();
		if (status != 0) {
			sb.append("AND a.status = ? ");
			paramList.add(status);
		}
		
		String appTimeStart=applicationEntity.getAppTimeStart();
		if (StringUtils.isNoneBlank(appTimeStart)) {
			sb.append("AND a.app_time >= ? ");
			paramList.add(appTimeStart);
		}
		String appTimeEnd=applicationEntity.getAppTimeEnd();
		if (StringUtils.isNoneBlank(appTimeEnd)) {
			sb.append("AND a.app_time <= ? ");
			paramList.add(appTimeEnd);
		}
		sb.append("ORDER BY id DESC ");
		sb.append("LIMIT ?, ? ");
		paramList.add((pageNo - 1) * pageSize);
		paramList.add(pageSize);
		
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement=connection.prepareStatement(sb.toString());
			for (int i = 0; i < paramList.size(); i++) {
				preparedStatement.setObject(i + 1, paramList.get(i));
			}
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				ApplicationEntity application=new ApplicationEntity();
				application.setId(resultSet.getInt("id"));
				application.setAppTime(resultSet.getDate("app_time"));
				application.setStatus(resultSet.getInt("status"));
				application.setHandleTime(resultSet.getDate("handle_time"));
				application.setPosition$name(resultSet.getString("position$name"));
				application.setTalent$name(resultSet.getString("talent$name"));
				application.setResume$intention(resultSet.getString("resume$intention"));
				
				applicationEntities.add(application);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return new PageModel<ApplicationEntity>(applicationEntities, getTotalRecords(applicationEntity), pageNo, pageSize);
	}
	/**
	 * 统计总记录数	
	 * 
	 * @param companyId
	 * @return
	 */
	private int getTotalRecords(ApplicationEntity applicationEntity){
		int totalRecords=0;
		List<Object> paramList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("COUNT(*) AS 'totalRecords' ");
		sb.append("FROM application a, resume r, position p, talent t,company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		sb.append("AND p.company_id = c.id ");
		sb.append("AND c.id = ? ");
		int companyId = applicationEntity.getCompanyId();
		paramList.add(companyId);
		
		String positionName = applicationEntity.getPosition$name();
		if (StringUtils.isNoneBlank(positionName)) {
			sb.append("AND p.name LIKE ? ");
			paramList.add("%" + positionName + "%");
		}
		
		int status = applicationEntity.getStatus();
		if (status != 0) {
			sb.append("AND a.status = ? ");
			paramList.add(status);
		}
		
		String appTimeStart=applicationEntity.getAppTimeStart();
		if (StringUtils.isNoneBlank(appTimeStart)) {
			sb.append("AND a.app_time >= ? ");
			paramList.add(appTimeStart);
		}
		String appTimeEnd=applicationEntity.getAppTimeEnd();
		if (StringUtils.isNoneBlank(appTimeEnd)) {
			sb.append("AND a.app_time <= ? ");
			paramList.add(appTimeEnd);
		}
		Connection connection=DBUtil.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		
		try {
			preparedStatement=connection.prepareStatement(sb.toString());
			DBUtil.setParameter(paramList, preparedStatement);
			resultSet=preparedStatement.executeQuery();
			resultSet.next();
			totalRecords=Integer.parseInt(resultSet.getString("totalRecords"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return totalRecords;
	}
	
	/**
	 * 企业审核求职者的申请
	 * 
	 * @param status
	 * @param applicationId
	 * @return
	 */
	public int verify(int status, int applicationId) {
		String sql = "UPDATE application SET status=?, handle_time=NOW() WHERE id=?";
		return DBUtil.executeUpdate(sql, status, applicationId);
	}

}
